/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.unesp.demac.bcc.soo.clarus.dao;

import br.unesp.demac.bcc.soo.clarus.to.ConsultaEmprestTO;
import br.unesp.demac.bcc.soo.clarus.util.FabricaConexao;
import java.sql.Connection;
import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author esaheki
 */
public class ConsultaEmprestTODAOImp implements ConsultaEmprestTODAO {
    
    @Override
    public List<ConsultaEmprestTO> getStats() {
        final String QUERY = "SELECT Obra.nomeObra as nomeObra, anoPublicacao, count(Emprestimo.idObra) as quantEmprestimos, "
                + "count(dataEmprestimo) as emprestimos, count(dataDevolucao) as devolucoes "
                + "FROM Emprestimo "
                + "INNER JOIN Obra ON Emprestimo.idObra = Obra.idObra "
                + "GROUP BY Emprestimo.idObra";
        Connection conn = FabricaConexao.getConexao();
        PreparedStatement pstmt = null;
        ResultSet res = null;
        List<ConsultaEmprestTO> stats = new ArrayList<ConsultaEmprestTO>();
        ConsultaEmprestTO to = null;
        
        if (conn != null) {
             try {
                
                pstmt = conn.prepareStatement(QUERY);
                res = pstmt.executeQuery();

                while (res.next()) {
                    to = new ConsultaEmprestTO();
                    to.setNomeObra(res.getString("nomeObra"));
                    to.setAnoPublicacao(res.getInt("anoPublicacao"));
                    to.setQuantEmprestimos(res.getInt("quantEmprestimos"));
                    int emprestimos = res.getInt("emprestimos");
                    int devolucoes = res.getInt("devolucoes");
                    to.setStatus((emprestimos == devolucoes) ? "Disponível" : "Reservado");
                    stats.add(to);
                }

            } catch (SQLException ex) {
                System.out.println("Erro ao recuperar obra: " + ex.getMessage());
            } finally {
                FabricaConexao.fechar(conn, pstmt);
            }
        }
        
        return stats;
        
    }
    
}
